insert overwrite table jms_dm.dm_waybill_prescription_reach_details_dt_tmp2 partition(dt)
select  taking_date     --    '揽收日期'
        ,island          --    '是否桃花岛'
        ,waybill_no       --    '运单号'
        ,plan_sign_date  -- 规划应签收日期
        ,customer_code     --    '客户编号'
        ,customer_name     --    '客户名称'
        ,start_subordinate_agent_code     --    '始发代理区code'
        ,start_subordinate_agent_name     --    '始发代理区name'
        ,start_parent_network_code     --    '始发加盟商code'
        ,start_parent_network_name     --    '始发加盟商name'
        ,start_regional_id     --    '始发大区id'
        ,start_regional_desc     --    '始发大区name'
        ,start_provider_id     --    '始发省份id'
        ,start_provider_desc     --    '始发省份name'
        ,start_city_id     --    '始发城市id'
        ,start_city_desc     --    '始发城市name'
        ,start_area_id     --    '始发区id'
        ,start_area_desc     --    '始发区name'
        ,start_pick_network_code     --    '始发网点code'
        ,start_pick_network_name     --    '始发网点name'
        ,aim_subordinate_agent_code     --    '目的代理区code'
        ,aim_subordinate_agent_name     --    '目的代理区name'
        ,aim_parent_network_code     --    '目的加盟商code'
        ,aim_parent_network_name     --    '目的加盟商name'
        ,aim_regional_id     --    '目的大区id'
        ,aim_regional_desc     --    '目的大区name'
        ,aim_provider_id     --    '目的省份id'
        ,aim_provider_desc     --    '目的省份name'
        ,aim_city_id     --    '目的城市id'
        ,aim_city_desc     --    '目的城市name'
        ,aim_area_id     --    '目的区id'
        ,aim_area_desc     --    '目的区name'
        ,aim_destination_code     --    '目的网点code'
        ,aim_destination_name     --    '目的网点name'
        ,taking_time     --    '揽收时间'
        ,warehous_time     --    '入仓时间'
        ,send_time     --    '发件扫描时间'
        ,start_center_code     --    '始发转运中心code'
        ,start_center_name    --    '始发转运中心name'
        ,start_centerarriva_time     --    '始发中心到件扫描时间'
        ,arrival_task_no     --    '到车任务号'
        ,type_one     --    '类型1'
        ,start_centersend_time     --    '始发中心发件扫描时间'
        ,departure_task     --    '始发发车任务单号'
        ,start_centersend_departure_time -- 始发中心发车时间
        ,aim_center_code     --    '目的转运中心code'
        ,aim_center_name     --    '目的转运中心name'
        ,aim_centerarriva_time     --    '目的中心到件扫描时间'
        ,arrival_task  -- 目的中心到车任务单号
        ,aim_centerarrival_departure_time -- 目的中心到车时间
        ,type_two     --    '类型2'
        ,aim_centersend_time     --    '目的中心发件扫描时间'
        ,sign_network_code     --    '签收网点code'
        ,sign_network_name     --    '签收网点name'
        ,sign_subordinate_agent_code     --    '签收代理区code'
        ,sign_subordinate_agent_name     --    '签收代理区name'
        ,sign_parent_network_code     --    '签收加盟商code'
        ,sign_parent_network_name     --    '签收加盟商name'
        ,sign_regional_id     --    '签收大区id'
        ,sign_regional_desc     --    '签收大区name'
        ,sign_provider_id     --    '签收省份id'
        ,sign_provider_desc     --    '签收省份name'
        ,sign_city_id     --    '签收城市id'
        ,sign_city_desc     --    '签收城市name'
        ,sign_area_id     --    '签收区id'
        ,sign_area_desc     --    '签收区name'
        ,pres_sign_time    --    '时效签收时间'
        ,real_sign_time     --    '实际签收时间'
        ,deliverytotaltime  -- 派件总耗时
        ,dispatch_sign_time  -- 派件应签收时间
        ,is_delivery_delay  -- 交件是否延误 0否1是
        ,is_transport_delay  -- 转运是否延误 0否1是
        ,is_sign_delay  -- 签收是否延误 0否1是
        ,case when is_delivery_delay=1 then '交件延误'
              when is_transport_delay=1 then '转运延误'
              when is_sign_delay=1 then '签收延误'
              else null end as delay_type -- 延误类型
        ,districtprescription  -- 区县时效
        ,prescription  -- 实际运行时效
        ,is_real_standard  -- 实际是否达标  1是0否
        ,pres_prescription  -- 时效运行时效
        ,is_pres_standard  -- 时效是否达标  1是0否
        ,case when pres_sign_time is not null then 1 else 0 end as is_pres_sign  -- 时效签收
        ,case when real_sign_time is not null then 1 else 0 end as is_real_sign  -- 实际签收
           ,economic_circle_type  --经济圈名称 1珠三角 2长三角 3京津冀
       ,taking_date as dt
   from (
 select  detail.taking_date     --    '揽收日期'
        ,detail.island          --    '是否桃花岛'
        ,detail.waybill_no       --    '运单号'
        ,detail.customer_code     --    '客户编号'
        ,detail.customer_name     --    '客户名称'
        ,detail.start_subordinate_agent_code     --    '始发代理区code'
        ,detail.start_subordinate_agent_name     --    '始发代理区name'
        ,detail.start_parent_network_code     --    '始发加盟商code'
        ,detail.start_parent_network_name     --    '始发加盟商name'
        ,detail.start_regional_id     --    '始发大区id'
        ,detail.start_regional_desc     --    '始发大区name'
        ,detail.start_provider_id     --    '始发省份id'
        ,detail.start_provider_desc     --    '始发省份name'
        ,detail.start_city_id     --    '始发城市id'
        ,detail.start_city_desc     --    '始发城市name'
        ,detail.start_area_id     --    '始发区id'
        ,detail.start_area_desc     --    '始发区name'
        ,detail.start_pick_network_code     --    '始发网点code'
        ,detail.start_pick_network_name     --    '始发网点name'
        ,detail.aim_subordinate_agent_code     --    '目的代理区code'
        ,detail.aim_subordinate_agent_name     --    '目的代理区name'
        ,detail.aim_parent_network_code     --    '目的加盟商code'
        ,detail.aim_parent_network_name     --    '目的加盟商name'
        ,detail.aim_regional_id     --    '目的大区id'
        ,detail.aim_regional_desc     --    '目的大区name'
        ,detail.aim_provider_id     --    '目的省份id'
        ,detail.aim_provider_desc     --    '目的省份name'
        ,detail.aim_city_id     --    '目的城市id'
        ,detail.aim_city_desc     --    '目的城市name'
        ,detail.aim_area_id     --    '目的区id'
        ,detail.aim_area_desc     --    '目的区name'
        ,detail.aim_destination_code     --    '目的网点code'
        ,detail.aim_destination_name     --    '目的网点name'
        ,detail.taking_time     --    '揽收时间'
        ,detail.warehous_time     --    '入仓时间'
        ,detail.send_time     --    '发件扫描时间'
        ,detail.start_center_code     --    '始发转运中心code'
        ,detail.start_center_name    --    '始发转运中心name'
        ,detail.start_centerarriva_time     --    '始发中心到件扫描时间'
        ,detail.arrival_task_no     --    '到车任务号'
        ,detail.type_one     --    '类型1'
        ,detail.start_centersend_time     --    '始发中心发件扫描时间'
        ,detail.departure_task     --    '始发发车任务单号'
        ,detail.start_centersend_departure_time -- 始发中心发车时间
        ,detail.aim_center_code     --    '目的转运中心code'
        ,detail.aim_center_name     --    '目的转运中心name'
        ,detail.aim_centerarriva_time     --    '目的中心到件扫描时间'
        ,detail.arrival_task  -- 目的中心到车任务单号
        ,detail.aim_centerarrival_departure_time -- 目的中心到车时间
        ,detail.type_two     --    '类型2'
        ,detail.aim_centersend_time     --    '目的中心发件扫描时间'
        ,detail.sign_network_code     --    '签收网点code'
        ,detail.sign_network_name     --    '签收网点name'
        ,detail.sign_subordinate_agent_code     --    '签收代理区code'
        ,detail.sign_subordinate_agent_name     --    '签收代理区name'
        ,detail.sign_parent_network_code     --    '签收加盟商code'
        ,detail.sign_parent_network_name     --    '签收加盟商name'
        ,detail.sign_regional_id     --    '签收大区id'
        ,detail.sign_regional_desc     --    '签收大区name'
        ,detail.sign_provider_id     --    '签收省份id'
        ,detail.sign_provider_desc     --    '签收省份name'
        ,detail.sign_city_id     --    '签收城市id'
        ,detail.sign_city_desc     --    '签收城市name'
        ,detail.sign_area_id     --    '签收区id'
        ,detail.sign_area_desc     --    '签收区name'
        ,detail.pres_sign_time    --    '时效签收时间'
        ,detail.real_sign_time     --    '实际签收时间'
        ,valid_data.deliverytotaltime  -- 派件总耗时
        ,concat(date_add(detail.taking_date,cast(valid_data.districtprescription as int)),' 23:59:59') as plan_sign_date  -- 规划应签收日期
        ,case when from_unixtime(unix_timestamp(detail.aim_centersend_time)+40*60,'HH:mm:ss')<=(case when valid_data.seconddispersetime is not null then from_unixtime(unix_timestamp(valid_data.seconddispersetime),'HH:mm:ss') else from_unixtime(unix_timestamp(valid_data.firstdispersetime),'HH:mm:ss') end)
              then concat(date_add(from_unixtime(unix_timestamp(detail.aim_centersend_time),'yyyy-MM-dd'),cast((valid_data.deliveryoutday+valid_data.deliveryextratime) as int)),' 23:59:00')
              when from_unixtime(unix_timestamp(detail.aim_centersend_time)+40*60,'HH:mm:ss')>(case when valid_data.seconddispersetime is not null then from_unixtime(unix_timestamp(valid_data.seconddispersetime),'HH:mm:ss') else from_unixtime(unix_timestamp(valid_data.firstdispersetime),'HH:mm:ss') end)
              then concat(date_add(from_unixtime(unix_timestamp(detail.aim_centersend_time),'yyyy-MM-dd'),cast((valid_data.deliveryoutday+valid_data.deliveryextratime+1) as int)),' 23:59:00')
         else null end as dispatch_sign_time  -- 派件应签收时间

        ,case when detail.start_centerarriva_time is null then null
              when (datediff(detail.real_sign_time,detail.taking_date)<=valid_data.districtprescription or datediff(detail.pres_sign_time,detail.taking_date)<=valid_data.districtprescription) then null
              when from_unixtime(unix_timestamp(concat(date_add(detail.taking_date,cast(valid_data.receivetotaltime as int)),from_unixtime(unix_timestamp(valid_data.sendtime),' HH:mm:ss')))-40*60,'yyyy-MM-dd HH:mm:ss')>detail.start_centerarriva_time then 1
              when from_unixtime(unix_timestamp(concat(date_add(detail.taking_date,cast(valid_data.receivetotaltime as int)),from_unixtime(unix_timestamp(valid_data.sendtime),' HH:mm:ss')))-40*60,'yyyy-MM-dd HH:mm:ss')<=detail.start_centerarriva_time then 0
         end as is_delivery_delay  -- 交件是否延误 0否1是


        ,case when detail.aim_centersend_time is null then null
              when (datediff(detail.real_sign_time,detail.taking_date)<=valid_data.districtprescription or datediff(detail.pres_sign_time,detail.taking_date)<=valid_data.districtprescription) then null
              when concat(date_add(from_unixtime(unix_timestamp(detail.start_centerarriva_time),'yyyy-MM-dd'), ((case when detail.start_centerarriva_time<=from_unixtime(unix_timestamp(detail.start_centersend_time)-40*60,'yyyy-MM-dd HH:mm:ss') then 0
              when detail.start_centerarriva_time>from_unixtime(unix_timestamp(detail.start_centersend_time)-40*60,'yyyy-MM-dd HH:mm:ss') then 1 end)+(case when from_unixtime(unix_timestamp(detail.aim_centerarriva_time),'HH:mm:ss')<= from_unixtime(unix_timestamp((case when valid_data.seconddispersetime is not null then from_unixtime(unix_timestamp(valid_data.seconddispersetime),'HH:mm:ss') else from_unixtime(unix_timestamp(valid_data.firstdispersetime),'HH:mm:ss') end))-40*60,'HH:mm:ss') then 0
              when from_unixtime(unix_timestamp(detail.aim_centerarriva_time),'HH:mm:ss')> from_unixtime(unix_timestamp((case when valid_data.seconddispersetime is not null then from_unixtime(unix_timestamp(valid_data.seconddispersetime),'HH:mm:ss') else from_unixtime(unix_timestamp(valid_data.firstdispersetime),'HH:mm:ss') end))-40*60,'HH:mm:ss') then 1 end)) ),case when valid_data.seconddispersetime is not null then from_unixtime(unix_timestamp(valid_data.seconddispersetime),' HH:mm:ss') else from_unixtime(unix_timestamp(valid_data.firstdispersetime),' HH:mm:ss') end)<detail.aim_centersend_time then 1 else 0 end as is_transport_delay  -- 转运是否延误 0否1是
         ,case when detail.real_sign_time is null then null
               when (datediff(detail.real_sign_time,detail.taking_date)<=valid_data.districtprescription or datediff(detail.pres_sign_time,detail.taking_date)<=valid_data.districtprescription) then null
               when date_add(detail.taking_date,cast(valid_data.districtprescription as int))<detail.real_sign_time then 1
               when date_add(detail.taking_date,cast(valid_data.districtprescription as int))>=detail.real_sign_time then 0 end as is_sign_delay  -- 签收是否延误 0否1是
         ,valid_data.districtprescription  -- 区县时效
         ,datediff(detail.real_sign_time,detail.taking_date) as prescription  -- 实际运行时效
         ,case when detail.real_sign_time is null and datediff(current_date(),detail.taking_date)>valid_data.districtprescription then 0
               when detail.real_sign_time is null and datediff(current_date(),detail.taking_date)<=valid_data.districtprescription then null
               when detail.real_sign_time is not null and datediff(detail.real_sign_time,detail.taking_date)<=valid_data.districtprescription then 1
               when detail.real_sign_time is not null and datediff(detail.real_sign_time,detail.taking_date)>valid_data.districtprescription then 0 end as is_real_standard  -- 实际是否达标  1是0否
         ,datediff(detail.pres_sign_time,detail.taking_date) as pres_prescription  -- 时效运行时效
         ,case when detail.pres_sign_time is null and datediff(current_date(),detail.taking_date)>valid_data.districtprescription then 0
               when detail.pres_sign_time is null and datediff(current_date(),detail.taking_date)<=valid_data.districtprescription then null
               when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,detail.taking_date)<=valid_data.districtprescription then 1
               when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,detail.taking_date)>valid_data.districtprescription then 0 end as is_pres_standard  -- 时效是否达标  1是0否
         ,case when detail.start_city_id in ('440600','440300','440100','441300','441900','440400','442000','441200','440700')
              and detail.sign_city_id in ('440600','440300','440100','441300','441900','440400','442000','441200','440700') then 1
              when detail.start_city_id in ('330100','310100','331000','330500','330200','320500','320200','330700','321200','320600','330600','320900','321000','320100','330900','321100','330400','320400','340100','340200','340500','340700','340800','341100','341700','341800')
              and detail.sign_city_id in ('330100','310100','331000','330500','330200','320500','320200','330700','321200','320600','330600','320900','321000','320100','330900','321100','330400','320400','340100','340200','340500','340700','340800','341100','341700','341800') then 2
              when detail.start_city_id in ('110100','120100','130100','130200','130400','130600','130900','130500','131000','130800','130700','131100','130300')
              and detail.sign_city_id in ('110100','120100','130100','130200','130400','130600','130900','130500','131000','130800','130700','131100','130300') then 3
              end economic_circle_type -- 经济圈名称 1珠三角 2长三角 3京津冀
    from
     (select * from jms_dm.dm_waybill_prescription_reach_details_dt_tmp1
       where dt>='{{ execution_date | date_add(-6) | cst_ds }}' and dt<='{{ execution_date |  cst_ds }}') detail
   left join (select * from jms_ods.tab_valid_data where dt='{{ execution_date |  cst_ds }}') valid_data
     on detail.start_city_desc=valid_data.fromcity
    and detail.start_area_desc=valid_data.fromdistrict
    and detail.sign_city_desc=valid_data.purposecity
    and detail.sign_area_desc=valid_data.purposedistrict
    and detail.taking_date>=from_unixtime(unix_timestamp(valid_data.effectivestarttime),'yyyy-MM-dd') and detail.taking_date<date_add(from_unixtime(unix_timestamp(valid_data.effectiveendtime),'yyyy-MM-dd'),1))t ;